package com.alingrad.iou;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.ContactsContract;

public class IOUDbAdapter {
	public static final String KEY_ID = "_id";
	public static final String KEY_TRANSACTION_DATE = "_date";
	public static final String KEY_TRANSACTION_PERSON_ID = "_contact_id";
	public static final String KEY_TRANSACTION_AMOUNT = "_amount";
	
	public static final String KEY_PEOPLE_NAME = "_name";
	public static final String KEY_PEOPLE_CONTACT_ID = "_contact_id";
	public static final String KEY_PEOPLE_BALANCE = "_balance";
	
	private static final String DATABASE_NAME = "Data";
	private static final String TRANSACTION_TABLE_NAME = "Transactions";
	private static final String PEOPLE_TABLE_NAME = "People";
	private static final int VERSION_NUMBER = 7;
	
	private Context mContext;
	
	private ContentResolver mCr;
	
	private DatabaseHelper mDatabase;
	private SQLiteDatabase mDb;
	
	private static class DatabaseHelper extends SQLiteOpenHelper {
		
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, VERSION_NUMBER);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("create table " + TRANSACTION_TABLE_NAME + " (" + 
					KEY_ID + " integer primary key autoincrement, " + 
					KEY_TRANSACTION_DATE + " text not null, " + 
					KEY_TRANSACTION_PERSON_ID + " integer not null, " +
					KEY_TRANSACTION_AMOUNT + " real not null);");
			
			db.execSQL("create table " + PEOPLE_TABLE_NAME + " (" +
					KEY_ID + " integer primary key autoincrement, " +
					KEY_PEOPLE_NAME + " text not null, " + 
					KEY_PEOPLE_CONTACT_ID + " integer not null, " +
					KEY_PEOPLE_BALANCE + " real not null);");
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + TRANSACTION_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + PEOPLE_TABLE_NAME);
            onCreate(db);
		}
	}
	
	public IOUDbAdapter(Context context) {
		mContext = context;
		mCr = context.getContentResolver();
	}
	
	public IOUDbAdapter open() throws SQLException {
		mDatabase = new DatabaseHelper(mContext);
        mDb = mDatabase.getWritableDatabase();
        return this;
	}
	
	public void close() {
		mDatabase.close();
	}
	
	public long createPerson(long contactId, String personName) {
		ContentValues initialValues = new ContentValues();
		
		initialValues.put(KEY_PEOPLE_NAME, personName);
		initialValues.put(KEY_PEOPLE_CONTACT_ID, contactId);
		initialValues.put(KEY_PEOPLE_BALANCE, 0f);
		
		return mDb.insert(PEOPLE_TABLE_NAME, null, initialValues);
	}
	
	public long createPerson(long contactId) {
		Cursor c = mCr.query(ContactsContract.Contacts.CONTENT_URI, 
				new String[] {ContactsContract.Contacts.DISPLAY_NAME}, 
				ContactsContract.Contacts._ID + " = " + contactId, 
				null, null);
		c.moveToFirst();
		String personName = c.getString(c.getColumnIndex(
				ContactsContract.Contacts.DISPLAY_NAME));
		
		ContentValues initialValues = new ContentValues();
		
		initialValues.put(KEY_PEOPLE_NAME, personName);
		initialValues.put(KEY_PEOPLE_CONTACT_ID, contactId);
		initialValues.put(KEY_PEOPLE_BALANCE, 0f);
		
		return mDb.insert(PEOPLE_TABLE_NAME, null, initialValues);
	}
	
	public void linkPerson(long personId, long contactId) {
		Cursor contact = mCr.query(ContactsContract.Contacts.CONTENT_URI,
				new String[] {ContactsContract.Contacts.DISPLAY_NAME}, 
				ContactsContract.Contacts._ID + " = " + contactId, 
				null, null);
		
		contact.moveToFirst();
		String name = contact.getString(
				contact.getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME));
		
		Cursor oldPerson = mDb.query(PEOPLE_TABLE_NAME, 
				new String[] {KEY_PEOPLE_CONTACT_ID, KEY_ID, KEY_PEOPLE_BALANCE}, 
				KEY_PEOPLE_CONTACT_ID + " = " + contactId,
				null, null, null, null);
		
		oldPerson.moveToFirst();
		
		ContentValues newValues = new ContentValues();
		
		newValues.put(KEY_PEOPLE_CONTACT_ID, contactId);
		newValues.put(KEY_PEOPLE_NAME, name);
		
		mDb.update(PEOPLE_TABLE_NAME, newValues, KEY_ID + " = " + personId, null);
		
		if (oldPerson != null && oldPerson.getCount() > 0) {
			final long oldPersonId = oldPerson.getLong(
					oldPerson.getColumnIndex(KEY_ID));
			final float balance = oldPerson.getFloat(
					oldPerson.getColumnIndex(KEY_PEOPLE_BALANCE));
			updatePersonBalance(personId, balance);
			
			ContentValues newTransactionValues = new ContentValues();
			
			newTransactionValues.put(KEY_TRANSACTION_PERSON_ID, personId);
			mDb.update(TRANSACTION_TABLE_NAME, newTransactionValues, KEY_TRANSACTION_PERSON_ID + " = " + oldPersonId, null);
			mDb.delete(PEOPLE_TABLE_NAME, KEY_ID + " = " + oldPersonId, null);
		}
	}
	
	public void unlinkPerson(long personId) {
		ContentValues newValues = new ContentValues();
		
		newValues.put(KEY_PEOPLE_CONTACT_ID, -1);
		
		mDb.update(PEOPLE_TABLE_NAME, newValues, KEY_ID + " = " + personId, null);
	}
	
	public Cursor getPerson(long personId) {
		return mDb.query(PEOPLE_TABLE_NAME, 
				new String[] {KEY_ID, KEY_PEOPLE_NAME, 
				KEY_PEOPLE_BALANCE, KEY_PEOPLE_CONTACT_ID},
				KEY_ID + " = " + personId,
				null, null, null, null);
	}
	
	public long getPersonIdFromContact(long contactId) {
		Cursor c = mDb.query(PEOPLE_TABLE_NAME,
				new String[] {KEY_ID},
				KEY_PEOPLE_CONTACT_ID + " = " + contactId,
				null, null, null, null);
		c.moveToFirst();
		if (c == null || c.getCount() <= 0)
			return -1;
		else
			return c.getLong(c.getColumnIndex(KEY_ID));
	}
	
	public long getPersonIdFromName(String name) {
		Cursor c = mDb.query(PEOPLE_TABLE_NAME,
				new String[] {KEY_ID},
				KEY_PEOPLE_NAME + " LIKE \'" + name + "\' AND " + KEY_PEOPLE_CONTACT_ID + " = -1",
				null, null, null, null);
		c.moveToFirst();
		if (c == null || c.getCount() <= 0)
			return -1;
		else
			return c.getLong(c.getColumnIndex(KEY_ID));
	}
	
	public long addTransaction(long personId, String date, float amount) {
		ContentValues initialValues = new ContentValues();
		
		initialValues.put(KEY_TRANSACTION_AMOUNT, amount);
        initialValues.put(KEY_TRANSACTION_DATE, date);
        initialValues.put(KEY_TRANSACTION_PERSON_ID, personId);

        updatePersonBalance(personId, amount);
        
        return mDb.insert(TRANSACTION_TABLE_NAME, null, initialValues);
	}
	
	private void updatePersonBalance(long personId, float amount) {
		Cursor c = mDb.query(PEOPLE_TABLE_NAME, 
				new String[] {KEY_PEOPLE_BALANCE}, 
				KEY_ID + " = " + personId,
				null, null, null, null);
		
		c.moveToFirst();
		final float newAmount = amount + c.getFloat(
				c.getColumnIndex(KEY_PEOPLE_BALANCE));
		
		ContentValues newValues = new ContentValues();
		
		newValues.put(KEY_PEOPLE_BALANCE, newAmount);
		
		mDb.update(PEOPLE_TABLE_NAME, newValues, KEY_ID + " = " + personId, null);
	}
	
	public long updateTransaction(long id, long personId, String date, float amount) {
		Cursor c = getTransaction(id);
		c.moveToFirst();
		final float amt = amount - c.getFloat(
				c.getColumnIndex(KEY_TRANSACTION_AMOUNT));
		c.close();
		ContentValues initialValues = new ContentValues();
		
		initialValues.put(KEY_ID, id);
		initialValues.put(KEY_TRANSACTION_AMOUNT, amount);
        initialValues.put(KEY_TRANSACTION_DATE, date);
        initialValues.put(KEY_TRANSACTION_PERSON_ID, personId);
        
        long retval = mDb.replace(TRANSACTION_TABLE_NAME, null, initialValues);
        if (retval != -1)
        	updatePersonBalance(personId, amt);
        return retval;
	}
	
	public boolean deleteTransaction(long id) {
		Cursor c = mDb.query(TRANSACTION_TABLE_NAME, 
				new String[] {KEY_TRANSACTION_AMOUNT, KEY_TRANSACTION_PERSON_ID}, 
				KEY_ID + " = " + id, 
				null, null, null, null);
		c.moveToFirst();
		final long personId = c.getLong(
				c.getColumnIndex(KEY_TRANSACTION_PERSON_ID));
		final float amount = c.getFloat(
				c.getColumnIndex(KEY_TRANSACTION_AMOUNT)); 
		
		if (mDb.delete(TRANSACTION_TABLE_NAME, KEY_ID + " = ?", new String[] {"" + id}) != 0) {
			updatePersonBalance(personId, -amount);
			return true;
		}
		return false;
	}
	
	public Cursor getTransaction(long id) {
		return mDb.query(TRANSACTION_TABLE_NAME, new String[] {KEY_ID, KEY_TRANSACTION_DATE,
				KEY_TRANSACTION_PERSON_ID, KEY_TRANSACTION_AMOUNT}, KEY_ID + " = ?", new String[] {"" + id}, 
				null, null, null);
	}
	
	public Cursor getTransactionsOfPerson(long personId) {
		return mDb.query(TRANSACTION_TABLE_NAME,
				new String[] {KEY_ID, KEY_TRANSACTION_DATE, KEY_TRANSACTION_AMOUNT},
				KEY_TRANSACTION_PERSON_ID + " = " + personId,
				null, null, null, KEY_TRANSACTION_DATE + " DESC, " + KEY_ID + " DESC");
	}
	
	public Cursor getTransactionHistory(int amount) {
		return mDb.query(TRANSACTION_TABLE_NAME, new String[] {KEY_ID, KEY_TRANSACTION_DATE,
                KEY_TRANSACTION_AMOUNT, KEY_TRANSACTION_PERSON_ID}, null, null, null, null, KEY_TRANSACTION_DATE + " DESC, " + KEY_ID + " DESC", "" + amount);
	}
	
	public Cursor getPeople() {
		return mDb.query(PEOPLE_TABLE_NAME, new String[] {KEY_ID, KEY_PEOPLE_BALANCE, KEY_PEOPLE_CONTACT_ID, KEY_PEOPLE_NAME},
				null, null, null, null, "abs(" + KEY_PEOPLE_BALANCE + ") DESC", null);
	}
}
